﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.EntityClient;
using UniversalDataAccess.BusinessEntities;

namespace UniversalDataAccess
{
    public class DataAccessBase
    {
        protected UniversityEntitiesContext dataContext;
        protected string dataBaseName;

        public DataAccessBase()
        { 
        }
        public DataAccessBase(string dbName, string conn, bool usePredefinedMetadata)
        {
            try
            {
                EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder(conn);

                if (usePredefinedMetadata)
                {
                    // Set the Metadata location.
                    SetMetadataLocation(entityBuilder);
                }
                EntityConnection entityConnection = new EntityConnection(entityBuilder.ToString());

                dataContext = new UniversityEntitiesContext(entityConnection);
                dataBaseName = dbName;               
            }
            catch
            {
                throw new Exception("Failed to connect to" + conn);
            }
        }
        public static bool TestConnection(string conn)
        {
            try
            {
                EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder(conn);
                 
                EntityConnection entityConnection = new EntityConnection(entityBuilder.ToString());

                entityConnection.Open();
            }
            catch(Exception ex)
            {
               //Failed to establish connection
                return false;
            }

            return true;
        }

        private static void SetMetadataLocation(EntityConnectionStringBuilder entityBuilder)
        {
            switch (entityBuilder.Provider)
            {
                case "FirebirdSql.Data.FirebirdClient":

                    entityBuilder.Metadata = @"|DataDirectory|\Schemas\UniversalModel.csdl||DataDirectory|\Schemas\UniversalModel FireBird.ssdl||DataDirectory|\Schemas\UniversalModel.msl"; 
                        break;


                case "System.Data.SqlClient":
                        entityBuilder.Metadata = @"|DataDirectory|\Schemas\UniversalModel.csdl||DataDirectory|\Schemas\UniversalModel SqlClient.ssdl||DataDirectory|\Schemas\UniversalModel.msl";
                    break;
            }
        }

        public List<UniViewFictiveNodePair> SelectGroupList(Int32 id)
        {
            List<TCLASSES> classes = dataContext.TCLASSES.ToList();
            List<TRULE_INSERT> rules = dataContext.TRULE_INSERT.ToList();

            var res = (from rule in rules
                       join cl in classes on rule.CHILD_CLASS_ID equals cl.ID
                       where rule.PARENT_CLASS_ID == id
                       select new UniViewFictiveNodePair
                       {
                           humanNamePl = cl.GROUP_NAME,
                           classId = cl.ID
                       }).ToList();
            return res;
        }

        public List<TObject> SelectTObjectsList()
        {
            try
            {
                List<TOBJECT> list = dataContext.TOBJECT.ToList();
                List<TCLASSES> classes = dataContext.TCLASSES.ToList();

                var res = (from tobj in list
                           join cl in classes on tobj.CLASS_ID equals cl.ID
                           select new TObject
                                      {
                                          DataBaseName = dataBaseName,
                                          CLASS_ID = tobj.CLASS_ID,
                                          EXT_ID = tobj.EXT_ID,
                                          ID = tobj.ID,
                                          NAME = tobj.NAME,
                                          PARENT_ID = tobj.PARENT_ID,
                                          GROUP_NAME = cl.GROUP_NAME
                                      }).ToList();

                return res;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public TObject SelectTObject_By_Id(int id)
        {
            try
            {
                List<TOBJECT> list = dataContext.TOBJECT.ToList();
               
            var res = (from tobj in list
                       where tobj.ID == id
                       select new TObject
                       {
                           DataBaseName = dataBaseName,
                           CLASS_ID = tobj.CLASS_ID,                          
                           EXT_ID = tobj.EXT_ID,
                           ID = tobj.ID,
                           NAME = tobj.NAME,
                           PARENT_ID = tobj.PARENT_ID
                       }).FirstOrDefault();

            return res;
            }
            catch(Exception ex)
            {
                throw new Exception("Error while SelectTObjectById");
            }
        }

        public TObject SelectTObject_By_Ext_Id(string ext_id)
        {
            try
            {
                List<TOBJECT> list = dataContext.TOBJECT.ToList();
               
                var res = (from tobj in list
                           where tobj.EXT_ID == ext_id
                           select new TObject
                           {
                               DataBaseName = dataBaseName,
                               CLASS_ID = tobj.CLASS_ID,
                               EXT_ID = tobj.EXT_ID,
                               ID = tobj.ID,
                               NAME = tobj.NAME,
                               PARENT_ID = tobj.PARENT_ID
                           }).FirstOrDefault();

                return res;
            }
            catch (Exception ex)
            {
                throw new Exception("Error while SelectTObjectBy_Ext_Id");
            }
        }

        public List<TObject> SelectTObjectList_By_Parent_Id(string parentId)
        {
            try
            {
                List<TOBJECT> list = dataContext.TOBJECT.ToList();
                List<TCLASSES> classes = dataContext.TCLASSES.ToList();

                var res = (from tobj in list
                           join cl in classes on tobj.CLASS_ID equals cl.ID
                           where tobj.PARENT_ID == parentId
                           select new TObject
                           {
                               DataBaseName = dataBaseName,
                               CLASS_ID = tobj.CLASS_ID,
                               EXT_ID = tobj.EXT_ID,
                               ID = tobj.ID,
                               NAME = tobj.NAME,
                               PARENT_ID = tobj.PARENT_ID,
                               GROUP_NAME = cl.GROUP_NAME
                           }).ToList();

                return res;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        public List<TObject> SelectTObjectList_By_Class_Id(int[] classIds)
        {
            try
            {
                List<TOBJECT> list = dataContext.TOBJECT.ToList();
                List<TCLASSES> classes = dataContext.TCLASSES.ToList();

                var res = (from tobj in list
                           join cl in classes on tobj.CLASS_ID equals cl.ID
                           where   classIds.Contains(tobj.CLASS_ID) && tobj.PARENT_ID != "-1"
                           select new TObject
                           {
                               DataBaseName = dataBaseName,
                               CLASS_ID = tobj.CLASS_ID,
                               EXT_ID = tobj.EXT_ID,
                               ID = tobj.ID,
                               NAME = tobj.NAME,
                               PARENT_ID = tobj.PARENT_ID,
                               GROUP_NAME = cl.GROUP_NAME
                           }).ToList();

                return res;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public void InsertTObject( TObject  tobject, ref int generated_id )
        {
            try
            {
                int new_Id = generated_id = ScopeIdentity + 1;
                TOBJECT tobj = new TOBJECT();
                tobj.ID = new_Id;
                tobj.CLASS_ID = tobject.CLASS_ID;
                tobj.EXT_ID = Guid.NewGuid().ToString();
                tobj.NAME = tobject.NAME;
                tobj.PARENT_ID = tobject.PARENT_ID;

                dataContext.AddToTOBJECT(tobj);
                dataContext.SaveChanges();                
            }
            catch(Exception ex)
            {
                throw new Exception("Error while InsertTObject");
            }
        }

        public void UpdateTObject(TObject tobject)
        {
            try
            {
                TOBJECT tobj = dataContext.TOBJECT.Where(t => t.ID == tobject.ID).FirstOrDefault();

                tobj.CLASS_ID = tobject.CLASS_ID;
                tobj.EXT_ID = tobject.EXT_ID;
                tobj.NAME = tobject.NAME;
                tobj.PARENT_ID = tobject.PARENT_ID;

                dataContext.SaveChanges();                
            }
            catch(Exception ex)
            {
                throw new Exception("Error while UpdateTObject");
            }
        }
        public void DeleteTObject(TObject tobject)
        {
            try
            {
                TOBJECT tobj = dataContext.TOBJECT.Where(t => t.ID == tobject.ID).FirstOrDefault();

                dataContext.DeleteObject(tobj);

                dataContext.SaveChanges();
            }
            catch (Exception ex)
            {
                throw new Exception("Error while DeleteTObject");
            }
        }

        public int ScopeIdentity
        {
            get
            {
                try
                {
                    List<TOBJECT> list = dataContext.TOBJECT.ToList();
                    var result =  (from tobj in list
                                    select tobj.ID).Max();
                         return result;
                }
                catch (Exception ex)
                {
                    throw;
                }
            }
        }

        public int ScopeIdentityForTRuleInsert
        {
            get
            {
                try
                {
                    List<TRULE_INSERT> list = dataContext.TRULE_INSERT.ToList();
                    if (list.Count == 0)
                    {
                        return 0;
                    }
                    var result = (from tobj in list
                                  select tobj.ID).Max();
                    return result;
                }
                catch (Exception ex)
                {
                    throw;
                }
            }
        }

        public string SelectClassFormNameByTobjectDBandClass_Id(int class_Id)
        {
            try
            {
                List<TCLASSES> list = dataContext.TCLASSES.ToList();
                TCLASSES tclass = list.Where(t => t.ID == class_Id).FirstOrDefault();

                return tclass != null ? tclass.FORM : ""; 
            }
            catch (Exception ex)
            {
                throw new Exception("Error while SelectClassFormNameByTobjectDBandClass_Id");
            }
        }

        public string SelectClassGroupNameByTobjectDBandClass_Id(int class_Id)
        {
            try
            {
                List<TCLASSES> list = dataContext.TCLASSES.ToList();
                TCLASSES tclass = list.Where(t => t.ID == class_Id).FirstOrDefault();

                return tclass != null ? tclass.GROUP_NAME : "";
            }
            catch (Exception ex)
            {
                throw new Exception("Error while SelectClassGroupNameByTobjectDBandClass_Id");
            }
        }

        public List<InsertRule> SelectRuleInsertByClass_Id(int class_Id)
        {
            try
            {
                List<TRULE_INSERT> list = dataContext.TRULE_INSERT.ToList();

                var result = (from tclass in list
                              where tclass.PARENT_CLASS_ID == class_Id
                              select new InsertRule
                              {
                                  ID = tclass.ID,
                                  PARENT_CLASS_ID = tclass.PARENT_CLASS_ID,
                                  CHILD_CLASS_ID = tclass.CHILD_CLASS_ID
                              }).ToList();

                return result;
            }
            catch (Exception ex)
            {
                throw new Exception("Error while SelectRuleInsertByClass_Id");
            }
        }
        public List<TClass> SelectChildRuleInsertClassesByParentClass_Id(int class_Id)
        {
            try
            {
                List<TRULE_INSERT> ruleList = dataContext.TRULE_INSERT.ToList();

                var filteredRuleList = (from trule in ruleList
                                        where trule.PARENT_CLASS_ID == class_Id
                              select new InsertRule
                              {
                                  ID = trule.ID,
                                  PARENT_CLASS_ID = trule.PARENT_CLASS_ID,
                                  CHILD_CLASS_ID = trule.CHILD_CLASS_ID
                              }).ToList();

                List<TCLASSES> listClasses = dataContext.TCLASSES.ToList();

                var filtereClessesList = (from tclass in listClasses
                                          join rule in filteredRuleList on tclass.ID equals rule.CHILD_CLASS_ID
                                          select new TClass
                                        {
                                           ID = tclass.ID,
                                            NAME = tclass.NAME,
                                              GROUP_NAME = tclass.GROUP_NAME,
                                               FORM = tclass.FORM,
                                                IS_CREATABLE = tclass.IS_CREATABLE ?? 1
                                        }).ToList();

                return filtereClessesList;
            }
            catch (Exception ex)
            {
                throw new Exception("Error while SelectChildRuleInsertClassesByParentClass_Id");
            }
        }

        #region Select / Insert/ Delete TRuleInsert

        public List<InsertRule> SelectTRuleInsertList()
        {
            try
            {
                List<TRULE_INSERT> list = dataContext.TRULE_INSERT
                    .OrderBy(t => t.PARENT_CLASS_ID )
                    .ThenBy(t => t.CHILD_CLASS_ID).ToList();
                var res = (from tobj in list
                           select new InsertRule
                           {
                               DataBaseName = dataBaseName,
                               ID = tobj.ID,
                               PARENT_CLASS_ID = tobj.PARENT_CLASS_ID,
                               CHILD_CLASS_ID = tobj.CHILD_CLASS_ID
                           }
                           ).ToList();

                return res;
            }
            catch (Exception ex)
            {
                throw new Exception("Error while SelectTRuleInsertList");
            }
        }

        public void InsertTRuleInsert(InsertRule insertRule, ref int generated_id)
        {
            try
            {
                int new_Id = generated_id = ScopeIdentityForTRuleInsert + 1;
                TRULE_INSERT tobj = new TRULE_INSERT();
                tobj.ID = new_Id;
                tobj.PARENT_CLASS_ID = insertRule.PARENT_CLASS_ID;
                tobj.CHILD_CLASS_ID = insertRule.CHILD_CLASS_ID;

                dataContext.AddToTRULE_INSERT(tobj);
                dataContext.SaveChanges();
            }
            catch (Exception ex)
            {
                throw new Exception("Error while InsertTRuleInsert");
            }
        }

        public void UpdateTRuleInsert(InsertRule insertRule)
        {
            try
            {
                TRULE_INSERT tobj = dataContext.TRULE_INSERT.Where(t => t.ID == insertRule.ID).FirstOrDefault();
                                
                tobj.PARENT_CLASS_ID = insertRule.PARENT_CLASS_ID;
                tobj.CHILD_CLASS_ID = insertRule.CHILD_CLASS_ID;

                dataContext.SaveChanges();
            }
            catch (Exception ex)
            {
                throw new Exception("Error while UpdateTRuleInsert");
            }
        }
        public void DeleteTRuleInsert(InsertRule insertRule)
        {
            try
            {
                TRULE_INSERT tobj = dataContext.TRULE_INSERT.Where(t => t.ID == insertRule.ID).FirstOrDefault();

                dataContext.DeleteObject(tobj);

                dataContext.SaveChanges();
            }
            catch (Exception ex)
            {
                throw new Exception("Error while DeleteTRuleInsert");
            }
        }

        #endregion

        #region Select  TClasses

        public List<TClass> SelectTClassList()
        {
            try
            {
                List<TCLASSES> list = dataContext.TCLASSES.ToList();
                var res = (from tobj in list
                           orderby tobj.ID
                           select new TClass
                           {                              
                               ID = tobj.ID,
                               NAME = tobj.NAME,
                               FORM = tobj.FORM,
                               GROUP_NAME = tobj.GROUP_NAME,
                               IS_CREATABLE  = tobj.IS_CREATABLE ?? 0,
                               DataBaseName = dataBaseName
                           }
                           ).ToList();

                return res;
            }
            catch (Exception ex)
            {
                throw new Exception("Error while SelectTClassList");
            }
        }

        #endregion

    }
}
